How to insert, query, and modify data in PostgreSQL (getting started with PostgreSQL, part three)

Once your PostgreSQL tables are built, the next step is putting them to work. This guide walks through the core data operations every PostgreSQL user needs: inserting records with INSERT, retrieving them with SELECT, filtering and combining tables with WHERE and JOIN, and modifying or removing data with UPDATE and DELETE.

You’ll also learn how PostgreSQL handles IDENTITY columns, sequences, aliases, and referential integrity.

How to insert (add) data to PostgreSQL

With tables in place, we can now add data our PostgreSQL databases. First, I’ll run through a few manual INSERT statements to show some of the behaviors. These scripts will be in addingdata_manual.sql – however, most of the data will get added by running just one script (addingdata.sql).

Before we start, make sure you’re logged in to psql: psql -d ham_shack -h localhost -U radiodev

The PostgreSQL INSERT statement

The basic behavior of the INSERT statement is pretty common across most data platforms. You define the table you’re going to add data to. You then supply a list of columns in that table. Finally, you supply values for those columns.

Here’s an example for the gear.antenna table:

This follows exactly what we described above. The only thing to point out here is that we didn’t include the antenna_id column, since that’s set as an IDENTITY column and will, by default, create its own values. So, there’s no need to add it to the column list.

However, if you want to override the behavior of IDENTITY, you can simply supply it. Just make sure you’re not violating the primary key or you’ll get an error. Don’t run this script, for example:

While this would technically work, it won’t reset the values on the IDENTITY column – you’ll get an error at the next INSERT.

Instead, you have to manually add data to an IDENTITY column that is less than the current value of the SEQUENCE (the underlying architecture of the IDENTITY behavior). Otherwise, you’d have to manually update the latest value for the SEQUENCE.

You don’t have to supply the column names. You can leave them off the INSERT statement entirely:

In order to skip the column list, you must have the right number of columns, in the right order, with the right data types, in your VALUES list. If not, you’ll get an error.

We also don’t want to mess up the IDENTITY value, we have to use the DEFAULT keyword, which triggers PostgreSQL to supply the value. For a very small table like gear.antenna – which by default only needs a single column -this is fine. However, it’d likely cause issues with larger tables.

You can also add more than one value – in this case, three rows are added to the table with:

Speaking of rows, the PostgreSQL documentation uses both ‘tuple’ and ‘row’ to describe rows. Tuple is a much more academic term but, to be clear, they both mean the same thing.

Filling out the rest of the data

Let’s exit interactive psql for the moment (using \q), and we’ll now load data into the remaining tables by running the addingdata.sql script.

First, copy it to the container volume. As covered previously in the guide, use the File app in Linux, File Explorer in Windows, or issue a command line to copy the file. Once done, execute it through psql by using
psql -d postgres -h localhost -U postgres -f "/var/lib/postgresql/bu/addingdata.sql"

Once complete, you’ll have a little bit of data in every table. One point from the script: the final way you can INSERT data is to combine it with a SELECT. The following query will run with the script, so no need to execute it. It shows how an INSERT and a SELECT can be combined:

Since there’s no WHERE clause and it’s doing a CROSS JOIN, every antenna is combined with every radio.

And we’re done! Now that we have data in the tables, we can proceed to query that data.

How to query data in PostgreSQL

In this section, I’ll walk through several different PostgreSQL queries, showing a few of the distinctions in how they operate compared to other platforms. This won’t be a complete guide on querying PostgreSQL – rather, just introduction to the fundamentals. If you do want more detail, click here for my full guide to querying PostgreSQL.

Before we start querying, be sure you’re logged in to psql as the role radiodev:
psql -d ham_shack -h localhost -U radiodev

How to retrieve data from PostgreSQL with the SELECT statement

PostgreSQL adheres fairly strictly to the ANSI SQL standard, which includes the use of the SELECT statement to retrieve data.

The most simple query is SELECT * FROM radio;, in which the * retrieves all columns. You have to define the tables you’re accessing through the FROM clause of the query. However, this isn’t the best way to write queries. Why?

First, and most importantly, I didn’t define the schema. It happens that the search_path (defined earlier in the guide) put the gear schema first – hence the correct table was found.

Next, the use of * is considered bad practice, since you’re not actually controlling specifically which columns you’re interested in, or their order. A better way to write the same query is:

Note that plenty of white space, tabs, carriage returns, and spaces are ignored by the query engine in PostgreSQL. To make the code easier to read, I format it with the carriage returns, etc, but this is optional of course. You could write the query as one long line and it would work the same – it’s just more difficult to read:
SELECT radio_id,radio_name,brand FROM gear.radio;

Here’s the output from any of the three queries defined so far:

The WHERE clause in PostgreSQL

Another important point is to filter data through the use of the WHERE clause. For example, if we want to only retrieve information about the ‘Maverick’ radio, we could do:

This leads to a much-reduced result set:

The WHERE clause supports logical operators as well. We could supply an OR in the WHERE clause like this:

Resulting in:

Unlike the integer value of 1 up above (for the strings), we’ve used a single quote around the string. It’s how PostgreSQL separates this from another object or function name. The other logical operators are AND and NOT. You can make increasingly more complex queries with these.

Before continuing with that, though, let’s add more tables to our queries through the JOIN operator.

The JOIN operator in PostgreSQL

PostgreSQL is an object-relational data store, and a relational storage engine lives on the relationships between tables. We defined those relationships when we created foreign keys and, to put tables together, we use the JOIN operator.

In addition to this, however, we need to define the owners. This is so the PostgreSQL optimizer knows which table we’re pulling columns from. A query to retrieve radio and antenna names might look like:

There are several points to take from this. First, we’re supplying the table name for each column now. This has to happen because two tables contain a radio_id column (in the form of radio and radio_antenna), and another two tables contain an antenna_id column with antenna and radio_antenna.

Second, we’ve added two more words to the query language: JOIN and ON. JOIN defaults to INNER JOIN. The INNER is syntactic sugar and not really needed. PostgreSQL supports LEFT and RIGHT joins as well. You can simply use LEFT JOIN instead of the more traditional LEFT OUTER JOIN to, again, eliminate syntactic sugar.

A LEFT or RIGHT join will return NULL values on non-matching rows. JOIN is how you tell which tables you’re going to add to the query. Then, ON is used to define the criteria for joining between the tables. In our case, we used the primary key from one table to join to the foreign key in the other.

To my eye, that query is quite hard to read because of all the table names, which isn’t unusual. Thankfully, PostgreSQL supports the use of aliases. So, to see my radio contacts for example, I could query like this:

This query then puts it all together and we’re still appropriately using both the schema and object ownership. However, by defining aliases (people.operator o), we can use a much shorter object “owner”.

Again, PostgreSQL doesn’t need to use the full syntax for aliases, people.operator AS o. We’ve also crossed between schemas, referencing both gear and people. The results should look something like:

Let’s add one more bit of syntax to our querying: placing the data in order.

How to use ORDER BY to order data in PostgreSQL

It’s common to want to control the order of data, and PostgreSQL never determines the order unless you specify it. So, running one query on different days could return the data in a different order, depending on how it’s stored. This is why syntax to determine the order is so important.

For example, to see frequencies in the people.contact table, we need to put them in order:

ORDER BY will, by default, place things in ascending order:

From there, you can control the order and the number of rows returned:

I added DESC after the column to tell it to go in descending order. Then, using LIMIT 5 ensures that only five rows are returned:

You can use LIMIT without an ORDER BYbut, again, you won’t be able to guarantee which five (or however many) rows you’ll see.

That’s some of the very basics of querying PostgreSQL for you, but there’s so much more we didn’t touch upon. As mentioned earlier, see my full guide for more.

As for what’s next, we’ll now take a look at data manipulation in PostgreSQL. By the end, you’ll know how to change and remove data from your PostgreSQL databases.

How to modify/manipulate data in PostgreSQL

As usual, make sure you’re connected to psql before continuing: psql -d ham_shack -h localhost -U radiodev

The UPDATE statement in PostgreSQL

The UPDATE statement syntax is extremely simple. You can only modify one table at a time, so you define the table you wish to change, then define the column(s) you’re changing. Finally, filter with a WHERE clause:

The last bit is the most important. You don’t have to use a WHERE clause but, if we’d left it off this query, every value in the column last_name would be changed to ‘Smith’. It’s not uncommon to do this intentionally, but it’s also very easy to do so accidentally.

So, BE CAUTIOUS! If your WHERE clauses matches multiple rows in the table, multiple rows will be updated.

When you execute the query through psql, you get a single line back – UPDATE 1– letting you know what you did and how many rows it affected.

If you want to update more than one column, simply add them to the SET part of the statement:

You can also add a FROM clause to the query, with which you can then JOIN to multiple tables. You’d do this to perform an UPDATE based on other data within the database. However, you’re still only ever updating one table at a time.

Finally, you can also use aliases for the table names – including the one being updated.

The DELETE statement in PostgreSQL

Let’s add a couple of rows, just so we can remove them later for a demonstration of how to delete data in PostgreSQL:

Like UPDATE, the DELETE statement in PostgreSQL is a single-table operation. The syntax is quite simple as well:

As with UPDATE, if you leave off the WHERE clause, wave goodbye to your data. When run, we get what we did, and the number of rows affected (similar to UPDATE): DELETE 1

But what if we wanted to get rid of the very first row in the antenna table? We’d run:

…which should return the error:

Since we have referential integrity in our database through the definition of foreign keys, we can’t just delete data that is in use; there’s a validation before the DELETE occurs.

Let’s run one more DELETE statement (just so we can see how more than one row is affected):

This returns DELETE 2.

You can also reference other tables in a DELETE statement with the USING clause. However, you’re again still only deleting from a single table. The other tables are to provide a more sophisticated filtering mechanism based on data in the database.

One final note on UPDATE and DELETE. In PostgreSQL, by default, when you modify or remove a row, you’re not really doing either of those things. In the case of both statements, the previous row is marked as logically deleted, and a new row is inserted into the table. No physical delete occurs.

This is all part of the Multi-Version Concurrency Control (MVCC), the mechanism within PostgreSQL that makes it so that readers seldom block writers, and writers seldom block readers. A process called VACUUM effectively ‘cleans up’ the logically-deleted rows, ultimately removing them from the system.

From here, there’s just one more (somewhat optional) thing I want to teach: how to set up the Bluebox sample database in PostgreSQL. Let’s get into it.

How to set up the Bluebox sample database in PostgreSQL (and why you should)

Ryan Booz created a fantastic PostgreSQL sample database, Bluebox. It represents a DVD rental kiosk (similar to the real one in the US, Redbox) and has a very sophisticated set of real data, tables, extensions, and more. It’s available as a dump that you can add to your own PostgreSQL cluster, or can be run as a container.

(In the container, pg_cron (a scheduling utility) runs scripts that simulate a real load.)

Before setting up Bluebox, I want to first show you how to clean up your PostgreSQL containers.

How to remove PostgreSQL containers

Containers are meant to live short lives. It’s normal to throw them out regularly and then start them back up again. Just remember, use volumes when you do so you can retain information once the container is gone.

If you’re still logged in to psql, run \q, then exit the Bash container with exit.

In PostgreSQL, you can only remove a container when it’s stopped, so step one is to stop the container:
docker stop StartingPostgreSQL

With the container stopped, you can now remove it with docker rm StartingPostgreSQL. This removes the container and everything in it – the only exception being anything stored in the local folder that you used as a volume.

The image will be retained unless you remove it too. To see the images on your machine, run docker images. You should see one called postgres:latest – the one we want to remove. To do so, use docker rmi postgres:latest.

And that’s it! Now, back to Bluebox.

How to set up the Bluebox sample database in PostgreSQL

To set up Bluebox in PostgreSQL, first head to the official GitHub repo if you haven’t already. Then, with whatever command line utility you’re using, navigate to the folder where you’re storing your Git repos, and clone the Bluebox one with git clone https://github.com/ryanbooz/bluebox-docker.git.

This will take several minutes, using around 338mb of storage. Once it’s done, change to the bluebox-docker folder: cd bluebox-docker

Then, it’s easy. Ryan even included a shell script: ./start.sh

The shell script walks you through selecting the version of PostgreSQL you need, and the port to install it to. If you’re still running the StartingPostgreSQL container, it’ll be using port 5432, so stop the container or pick another port:

Then, it will download the docker image, start the container, and load the database, all completely automatically. This step also takes several minutes on a good internet connection.

When you’re done, you’ll have a running container with Bluebox in it. The roles for connecting are as follows:

Ryan’s script even helpfully pops out a psql connection string: psql -h localhost -p 5432 -U bb_admin -d bluebox

And that’s it for the series! You have been introduced to PostgreSQL. Have fun!